//
//  Sqlite3_helper.swift
//  C150729_sqlite
//
//  Created by yao_yu on 15/7/30.
//  Copyright © 2015年 yao_yu. All rights reserved.
//

import Foundation

extension NSRange {
    var isValid:Bool {
        return location >= 0 && length >= 0
    }
}

extension NSString {
    func subStringFromString(searchString:String) -> NSString {
        let findRange = rangeOfString(searchString)
        if findRange.isValid {
            return substringFromIndex(findRange.location + findRange.length)
        }
        return ""
    }
    
    func subStringToString(searchString:String) -> NSString {
        let findRange = rangeOfString(searchString)
        if findRange.isValid {
            return substringToIndex(findRange.location)
        }
        return ""
    }
}

let SQLITE_DATE = SQLITE_NULL + 1

private typealias DataConvertor = (COpaquePointer, CInt)->AnyObject?

@objc class Query:NSObject {
    var statement: COpaquePointer = nil
    var db:COpaquePointer = nil
    var columnCount:Int32 = 0
    var columnNames = [String]()
    var columnTypes = [Int32]()
    private var dataTypeMap = [String:Int32]()
    private var dataConvertor = [Int32: DataConvertor]()
    
    init(db:COpaquePointer){
        super.init()
        self.db = db
        self.buildTypeIndex()
    }
    
    private func buildTypeIndex(){
        // Column types - http://www.sqlite.org/datatype3.html (section 2.2 table column 1)
        let blobTypes = ["BINARY", "BLOB", "VARBINARY"]
        let charTypes = ["CHAR", "CHARACTER", "CLOB", "NATIONAL VARYING CHARACTER", "NATIVE CHARACTER", "NCHAR", "NVARCHAR", "TEXT", "VARCHAR", "VARIANT", "VARYING CHARACTER"]
        let dateTypes = ["DATE", "DATETIME", "TIME", "TIMESTAMP"]
        let intTypes  = ["BIGINT", "BIT", "BOOL", "BOOLEAN", "INT", "INT2", "INT8", "INTEGER", "MEDIUMINT", "SMALLINT", "TINYINT"]
        let nullTypes = ["NULL"]
        let realTypes = ["DECIMAL", "DOUBLE", "DOUBLE PRECISION", "FLOAT", "NUMERIC", "REAL"]
        // Determine type of column - http://www.sqlite.org/c3ref/c_blob.html
        for (typeStrings, sqlDataType) in [
            (blobTypes, SQLITE_BLOB),
            (charTypes, SQLITE_TEXT),
            (dateTypes, SQLITE_DATE),
            (nullTypes, SQLITE_NULL),
            (intTypes, SQLITE_INTEGER),
            (realTypes, SQLITE_FLOAT),
            ] {
            for typeString in typeStrings {
                dataTypeMap[typeString] = sqlDataType
            }
        }
    }
    
    private func registerTypeConverter() {
        dataConvertor[SQLITE_INTEGER] = getColumnIntValue
        dataConvertor[SQLITE_FLOAT] = getColumnFloatValue
        dataConvertor[SQLITE_NULL] = getColumnNULLValue
        dataConvertor[SQLITE_DATE] = getColumnDateValue
        dataConvertor[SQLITE_TEXT] = getColumnTextValue
        dataConvertor[SQLITE_BLOB] = getColumnNSDataValue
    }
    
    func query(sql:String) -> [[AnyObject]]? {
        if (sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK){
            print("选择成功")
            columnNames.removeAll()
            let isRow = sqlite3_step(statement) == SQLITE_ROW
            if isRow{
                columnCount = sqlite3_column_count(statement)
                for index in 0..<columnCount {
                    let name = sqlite3_column_name(statement, index)
                    columnNames.append(String.fromCString(name)!)
                    columnTypes.append(getColumnType(statement, atIndex: index))
                }
                
            }
            var rowData = [AnyObject]()
            repeat {
                for index in 0..<columnCount {
                    
                }
            } while (sqlite3_step(statement) == SQLITE_ROW)
            
//                let director = sqlite3_column_text(statement, 1)
//                let buf_director = String.fromCString(UnsafePointer<CChar>(director))
//                let imagen = sqlite3_column_text(statement, 2)
//                let buf_imagen = String.fromCString(UnsafePointer<CChar>(imagen))
//                let titulo = sqlite3_column_text(statement, 3)
//                let buf_titulo = String.fromCString(UnsafePointer<CChar>(titulo))
//                let year = sqlite3_column_text(statement, 4)
//                let buf_year = String.fromCString(UnsafePointer<CChar>(year))
//                
//                print( ["director": buf_director!, "imagen": buf_imagen!, "titulo": buf_titulo!, "year": buf_year!])
//            } while (sqlite3_step(statement) == SQLITE_ROW)
            sqlite3_finalize(statement)
        }
        return nil
    }
    
    private func getColumnType(stmt:COpaquePointer, atIndex index:CInt)->CInt {
        let buf = sqlite3_column_decltype(stmt, index)
        if buf != nil {
            let _typeString:NSString = String.fromCString(buf)!.uppercaseString
            let typeString = _typeString.subStringToString("(") as String

            if let dataType = dataTypeMap[typeString] {
                return dataType
            }
            return SQLITE_TEXT
        } else {
            // For expressions and sub-queries
            return sqlite3_column_type(stmt, index)
        }
    }
    
    func getColumnIntValue(stmt:COpaquePointer, index:CInt)->AnyObject? {
        return Int(sqlite3_column_int(stmt, index))
    }
    
    func getColumnFloatValue(stmt:COpaquePointer, index:CInt)->AnyObject? {
        return sqlite3_column_double(stmt, index)
    }
    
    func getColumnNSDataValue(stmt:COpaquePointer, index:CInt)->AnyObject? {
        let data = sqlite3_column_blob(stmt, index)
        let size = sqlite3_column_bytes(stmt, index)
        return NSData(bytes:data, length: Int(size))
    }
    
    func getColumnNULLValue(stmt:COpaquePointer, index:CInt) -> AnyObject? {
        return nil
    }
    
    func getColumnTextValue(stmt:COpaquePointer, index:CInt) -> AnyObject? {
        let buf = UnsafePointer<Int8>(sqlite3_column_text(stmt, index))
        return String.fromCString(buf)
    }
    
    func getColumnDateValue(stmt:COpaquePointer, index:CInt) -> AnyObject? {
        let txt = UnsafePointer<Int8>(sqlite3_column_text(stmt, index))
        if txt != nil {
            if let buf = NSString(CString:txt, encoding:NSUTF8StringEncoding) {
                let set = NSCharacterSet(charactersInString: "-:")
                let range = buf.rangeOfCharacterFromSet(set)
                if range.location != NSNotFound {
                    // Convert to time
                    var time:tm = tm(tm_sec: 0, tm_min: 0, tm_hour: 0, tm_mday: 0, tm_mon: 0, tm_year: 0, tm_wday: 0, tm_yday: 0, tm_isdst: 0, tm_gmtoff: 0, tm_zone:nil)
                    strptime(txt, "%Y-%m-%d %H:%M:%S", &time)
                    time.tm_isdst = -1
                    let diff = NSTimeZone.localTimeZone().secondsFromGMT
                    let t = mktime(&time) + diff
                    let ti = NSTimeInterval(t)
                    let val = NSDate(timeIntervalSince1970:ti)
                    return val
                }
            }
        }
        // If not a text date, then it's a time interval
        let val = sqlite3_column_double(stmt, index)
        return NSDate(timeIntervalSince1970: val)
    }
    
    
    // Get column value
    private func getColumnValue(stmt:COpaquePointer, index:CInt, type:CInt)->AnyObject? {
        // Integer
        if type == SQLITE_INTEGER {
            let val = sqlite3_column_int(stmt, index)
            return Int(val)
        }
        // Float
        if type == SQLITE_FLOAT {
            let val = sqlite3_column_double(stmt, index)
            return Double(val)
        }
        // Text - handled by default handler at end
        // Blob
        if type == SQLITE_BLOB {
            let data = sqlite3_column_blob(stmt, index)
            let size = sqlite3_column_bytes(stmt, index)
            let val = NSData(bytes:data, length: Int(size))
            return val
        }
        // Null
        if type == SQLITE_NULL {
            return nil
        }
        // Date
        if type == SQLITE_DATE {
            // Is this a text date
            let txt = UnsafePointer<Int8>(sqlite3_column_text(stmt, index))
            if txt != nil {
                if let buf = NSString(CString:txt, encoding:NSUTF8StringEncoding) {
                    let set = NSCharacterSet(charactersInString: "-:")
                    let range = buf.rangeOfCharacterFromSet(set)
                    if range.location != NSNotFound {
                        // Convert to time
                        var time:tm = tm(tm_sec: 0, tm_min: 0, tm_hour: 0, tm_mday: 0, tm_mon: 0, tm_year: 0, tm_wday: 0, tm_yday: 0, tm_isdst: 0, tm_gmtoff: 0, tm_zone:nil)
                        strptime(txt, "%Y-%m-%d %H:%M:%S", &time)
                        time.tm_isdst = -1
                        let diff = NSTimeZone.localTimeZone().secondsFromGMT
                        let t = mktime(&time) + diff
                        let ti = NSTimeInterval(t)
                        let val = NSDate(timeIntervalSince1970:ti)
                        return val
                    }
                }
            }
            // If not a text date, then it's a time interval
            let val = sqlite3_column_double(stmt, index)
            let dt = NSDate(timeIntervalSince1970: val)
            return dt
        }
        // If nothing works, return a string representation
        let buf = UnsafePointer<Int8>(sqlite3_column_text(stmt, index))
        let val = String.fromCString(buf)
        //		println("SQLiteDB - Got value: \(val)")
        return val
    }
}